﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;
using IFSModel;
using Newtonsoft.Json;
namespace IFSController
{
    public class score
    {
        public static bool AddLiveScore(List<liveScore> data)
        {
           
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    //string deleteProp = "DELETE FROM liveScore";
                    //dataContext.ExecuteCommand(deleteProp);

                    var leagues = data.GroupBy(t => t.gl).Select(g => new leagueLive() { name = g.First().gl, modifyDate = DateTime.Now });

                    var matchs =
                        data.Select(
                            g =>
                            new match
                                {
                                    matchId = g.bh,
                                    isFinish = g.isFinishLive,
                                    liveA = g.live_a,
                                    liveB = g.live_b,
                                    matchTime = g.sj,
                                    score = g.bc,
                                    memo = g.memo,
                                    resume = g.resume,
                                    status = g.isstart,
                                    modifyDate = DateTime.Now,
                                    leagueName = g.gl
                                });
                    var prices =
                        data.Select(
                            g =>
                            new matchPrice
                                {
                                    matchID = g.bh,
                                    pk2 = g.pk2,
                                    euro1x2 = g.euro1x2,
                                    rq = g.rq,
                                    sw1 = g.sw1,
                                    sx = g.sx,
                                    sw2 = g.sw2,
                                    sy = g.sy,
                                    mb_pk = string.IsNullOrEmpty(g.mb_pk) ? -99 : Convert.ToDecimal(g.mb_pk),
                                    cb_pk = string.IsNullOrEmpty(g.cb_pk) ? -99 : Convert.ToDecimal(g.cb_pk),
                                    modifyDate = DateTime.Now
                                });
                    foreach (var league in leagues)
                    {

                        if (dataContext.leagueLives.Any(l => l.name == league.name))
                        {
                            var oldLeague =
                                           dataContext.leagueLives.Single(prop => prop.name == league.name);
                            if (oldLeague != null)
                            {
                                oldLeague.name = league.name;
                                oldLeague.modifyDate = league.modifyDate;
                                oldLeague.isEnable = league.isEnable;
                            }
                        }
                        else
                        {
                            dataContext.leagueLives.InsertOnSubmit(league);
                        }

                    }
                    dataContext.SubmitChanges();
                    foreach (var match in matchs)
                    {
                        
                        if (dataContext.matches.Any(m => m.matchId == match.matchId))
                        {
                            var oldMatch =
                                           dataContext.matches.Single(prop => prop.matchId == match.matchId);
                            if (oldMatch != null)
                            {
                                oldMatch.isEnable = match.isEnable;
                                oldMatch.isFinish = match.isFinish;
                                oldMatch.matchTime = match.matchTime;
                                oldMatch.memo = match.memo;
                                oldMatch.modifyDate = DateTime.Now;
                                oldMatch.resume = match.resume;
                                oldMatch.score = match.score;
                                oldMatch.status = match.status;
                                
                            }
                        }
                        else
                        {
                            dataContext.matches.InsertOnSubmit(match);
                        }
                        
                    }
                    dataContext.SubmitChanges();
                    foreach (var price in prices)
                    {
                        if (dataContext.matchPrices.Any(mp => mp.matchID == price.matchID))
                        {
                            var oldprice =
                                           dataContext.matchPrices.Single(prop => prop.matchID == price.matchID);
                            if (oldprice != null)
                            {
                                oldprice.euro1x2 = price.euro1x2;
                                oldprice.modifyDate = DateTime.Now;
                                oldprice.pk2 = price.pk2;
                                oldprice.rq = price.rq;
                                oldprice.sw1 = price.sw1;
                                oldprice.sw2 = price.sw2;
                                oldprice.sx = price.sx;
                                oldprice.sy = price.sy;
                                oldprice.mb_pk = price.mb_pk;
                                oldprice.cb_pk = price.cb_pk;
                            }
                        }
                        else
                        {
                            dataContext.matchPrices.InsertOnSubmit(price);
                        }
                        
                    }
                    dataContext.SubmitChanges();
                    //dataContext.liveScores.InsertAllOnSubmit(data);
                    //dataContext.matches.AttachAll(matchs);
                    //dataContext.matches.InsertAllOnSubmit(matchs);
                    //dataContext.matchPrices.AttachAll(prices);
                    //dataContext.SubmitChanges();
                    return true;
                }
                catch (Exception ex)
                {

                    return false;
                }

            }
        }
        public static string GetMatch()
        {
            var serializer = new JavaScriptSerializer();
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    var mathcs = (from matches in dataContext.matches
                                  select matches).ToList();

                    return serializer.Serialize(mathcs);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                            username = "getMatch function",
                        logType = 1
                    });
                    return null;
                }
            }
        }

        public static string GetLeague(string leagueType)
        {
            var serializer = new JavaScriptSerializer();
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    var Leagues = from ll in dataContext.leagueLives
                                  join l in dataContext.leagues
                                  on ll.name equals  l.name
                                  orderby l.priority 
                                  select l;
                    if (!string.IsNullOrEmpty(leagueType ))
                    {
                        Leagues = Leagues.Where(l => l.type.ToString() == leagueType);
                    }

                    return serializer.Serialize(Leagues);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "GetLeague",
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static string GetMatch(string lengue)
        {
            var serializer = new JavaScriptSerializer();
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    var mathcs = (from matches in dataContext.matches
                                  where matches.leagueName.Trim() == lengue.Trim()
                                  select matches).ToList();

                    return serializer.Serialize(mathcs);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch function",
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static string GetMatchPrices()
        {
            using (var dataContext = new IFNDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var mathcs = (from matches in dataContext.matches
                                  join price in dataContext.matchPrices
                                      on matches.matchId equals price.matchID
                                      where price.rq != -99 && price.mb_pk != -99 && price.cb_pk != -99
                                  join l in dataContext.leagues on matches.leagueName equals l.name
                                  select new
                                             {
                                                 matches,
                                                 price = new
                                                 {
                                                     price.euro1x2,
                                                     price.id,
                                                     price.isEnable,
                                                     price.matchID,
                                                     price.modifyDate,
                                                     price.pk2,
                                                     rq = getRq(price.rq, price.mb_pk, price.cb_pk),
                                                     price.sw1,
                                                     price.sw2,
                                                     price.sx,
                                                     price.sy,
                                                 },
                                                 l
                                             }).ToList();

                    return serializer.Serialize(mathcs);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch GetMatchPrices",
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static string GetMatchPrices(string lengue)
        {
            using (var dataContext = new IFNDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var mathcs = (from matches in dataContext.matches
                                  join price in dataContext.matchPrices
                                      on matches.matchId equals price.matchID
                                  where price.rq != -99 && price.mb_pk != -99 && price.cb_pk != -99
                                  join l in dataContext.leagues on matches.leagueName equals l.name
                                      where matches.leagueName.Trim() == lengue.Trim()
                                  select new
                                  {
                                      matches,
                                      price = new
                                      {
                                          price.euro1x2,
                                          price.id,
                                          price.isEnable,
                                          price.matchID,
                                          price.modifyDate,
                                          price.pk2,
                                          rq = getRq(price.rq, price.mb_pk, price.cb_pk),
                                          price.sw1,
                                          price.sw2,
                                          price.sx,
                                          price.sy,
                                      },
                                      l
                                  }).ToList();

                    return serializer.Serialize(mathcs);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch GetMatchPrices",
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static string GetMatch(DateTime date)
        {
            using (var dataContext = new IFNDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var mathcs = (from matches in dataContext.matches
                                  where matches.matchTime.Value.Date == date.Date
                                  select matches).ToList();

                    return serializer.Serialize(mathcs);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch function",
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static string GetMatchPrices(DateTime date)
        {
            using (var dataContext = new IFNDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var mathcs = (from matches in dataContext.matches
                                  where matches.matchTime.Value.Date == date.Date
                                  join price in dataContext.matchPrices
                                      on matches.matchId equals price.matchID
                                  where price.rq != -99 && price.mb_pk != -99 && price.cb_pk != -99
                                  join l in dataContext.leagues on matches.leagueName equals l.name
                                  select new
                                  {
                                      matches,
                                      price = new {price.euro1x2,
                                      price.id,
                                      price.isEnable,
                                      price.matchID,
                                      price.modifyDate,
                                      price.pk2,
                                      rq = getRq(price.rq, price.mb_pk, price.cb_pk),
                                      price.sw1,
                                      price.sw2,
                                      price.sx,
                                      price.sy,
                                      },
                                      l

                                  }).ToList();

                    return serializer.Serialize(mathcs);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch GetMatchPrices",
                        logType = 1
                    });
                    return null;
                }
            }
        }
        private static decimal? getRq(decimal? rq, decimal? mb, decimal? cb)
        {
            var result = rq != null && rq != -99 ? rq : mb != null && mb != -99 ? mb : cb;
            result = result < 0 ? result * -1 : result;
            return result;
        }
        public static string GetMatchPrices(string leagues, string matchDateScore, string matchSinceScore, string matchUntilScore)
        {
            using (var dataContext = new IFNDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var mathcs = from matches in dataContext.matches
                                  select matches;
                                  
                    if (!string.IsNullOrEmpty(matchDateScore))
                    {
                        var date = JsonConvert.DeserializeObject<DateTime>(matchDateScore);
                        mathcs = from mdate in mathcs
                                 where mdate.matchTime.Value.Date == date.ToUniversalTime().AddHours(7).Date
                                 select mdate;
                    }
                    if (!string.IsNullOrEmpty(matchSinceScore))
                    {
                        var sinceDate = JsonConvert.DeserializeObject<DateTime>(matchSinceScore);
                        mathcs = from mdate in mathcs
                                 where mdate.matchTime.Value >= sinceDate.ToUniversalTime().AddHours(7) // change to bangkok time
                                 select mdate;
                    }
                    if (!string.IsNullOrEmpty(matchUntilScore))
                    {
                        var UntilDate = JsonConvert.DeserializeObject<DateTime>(matchUntilScore);
                        mathcs = from mdate in mathcs
                                 where mdate.matchTime.Value < UntilDate.ToUniversalTime().AddHours(7)
                                 select mdate;
                    }
                    if (!string.IsNullOrEmpty(leagues))
                    {
                        mathcs = from mdate in mathcs
                                 where mdate.leagueName.Trim().ToLower() == leagues.Trim().ToLower()
                                 select mdate;
                    }
                    var result = from matches in mathcs
                        join price in dataContext.matchPrices
                                      on matches.matchId equals price.matchID
                                  where price.rq != -99 || price.mb_pk != -99 || price.cb_pk != -99
                                  join l in dataContext.leagues on matches.leagueName equals l.name
                                  orderby l.priority , matches.matchTime 
                                  select new
                                  {
                                      matches,
                                      price = new
                                      {
                                          price.euro1x2,
                                          price.id,
                                          price.isEnable,
                                          price.matchID,
                                          price.modifyDate,
                                          price.pk2,
                                          rq = getRq(price.rq, price.mb_pk, price.cb_pk),
                                          price.sw1,
                                          price.sw2,
                                          price.sx,
                                          price.sy,
                                      },
                                      l
                                  };

                    return serializer.Serialize(result);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch GetMatchPrices",
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static string GetMatch(DateTime since, DateTime untill)
        {
            using (var dataContext = new IFNDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var mathcs = (from matches in dataContext.matches
                                  where (matches.matchTime.Value >= since &&  matches.matchTime.Value.Date <= untill.Date)
                                  select matches).ToList();

                    return serializer.Serialize(mathcs);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch function",
                        logType = 1
                    });
                    return null;
                }
            }
        }
        public static string GetMatchPrices(DateTime since, DateTime untill)
        {
            using (var dataContext = new IFNDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var mathcs = (from matches in dataContext.matches
                                  where (matches.matchTime.Value >= since && matches.matchTime.Value.Date <= untill.Date)
                                  join price in dataContext.matchPrices
                                      on matches.matchId equals price.matchID
                                  where price.rq != -99 || price.mb_pk != -99 || price.cb_pk != -99
                                  join l in dataContext.leagues on matches.leagueName equals l.name
                                  select new
                                  {
                                      matches,
                                      price = new
                                      {
                                          price.euro1x2,
                                          price.id,
                                          price.isEnable,
                                          price.matchID,
                                          price.modifyDate,
                                          price.pk2,
                                          rq = getRq(price.rq, price.mb_pk, price.cb_pk),
                                          price.sw1,
                                          price.sw2,
                                          price.sx,
                                          price.sy,
                                      },
                                      l
                                  });

                    return serializer.Serialize(mathcs);
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "getMatch GetMatchPrices",
                        logType = 1
                    });
                    return null;
                }
            }
        }


        
    }
}
